from db import Base, engine
from typing import List
from sqlalchemy import String, insert, update, select, delete, ForeignKey, Table, Column
from sqlalchemy.orm import Mapped, mapped_column, Session, relationship, sessionmaker
from sys.dtos import RoleDto, UserDto
from common import PageResult, PageParams

# Session = sessionmaker(engine)

assocation_table = Table(
    "user_role",
    Base.metadata,
    Column("user_id", ForeignKey("user.id"), primary_key=True),
    Column("role_id", ForeignKey("role.id"), primary_key=True)
)


class Role(Base):
    __tablename__ = "role"

    id: Mapped[int] = mapped_column(primary_key=True, nullable=False)
    name: Mapped[str] = mapped_column(String(200))

    def __repr__(self):
        print(f"id:{self.id}   name:{self.name}")


class User(Base):
    __tablename__ = "user"

    id: Mapped[int] = mapped_column(primary_key=True, nullable=False)
    name: Mapped[str] = mapped_column(String(200))
    password: Mapped[str] = mapped_column(String(100))

    roles: Mapped[List["Role"]] = relationship(secondary=assocation_table, lazy=False)
    cars: Mapped[List["Car"]] = relationship(lazy=False)


Base.metadata.create_all(engine)


def role_dto2model(item: RoleDto):
    return Role(id=item.id, name=item.name)


def user_dto2model(item: UserDto):
    # User(id=item.id) user如果设置了id，则不会自动生成主键，所以不设置。
    user = User(name=item.name, password=item.password)
    roles = get_roles(item.roleIds)
    if roles:
        user.roles = roles
    return user


# =====================   role   =======================


def insert_role(item: RoleDto):
    role = role_dto2model(item)
    with Session(engine) as session, session.begin():
        session.add(role)


def update_role(item: RoleDto):
    with Session(engine) as session, session.begin():
        return session.query(Role).filter(Role.id == item.id).update(
            {"name": item.name}
        )


def delete_role(id: int):
    with Session(engine) as session, session.begin():
        return session.query(Role).filter(Role.id == id).delete()


def get_role(id: int):
    with Session(engine) as session:
        role = session.query(Role).filter(Role.id == id).first()
        return RoleDto.from_orm(role)


def get_roles(ids: List[int]):
    with Session(engine) as session:
        roles = session.query(Role).filter(Role.id.in_(ids)).all()
        return roles


def role_page(params: PageParams):
    with Session(engine) as session:
        offset = (params.page - 1) * params.size
        sql_where = []
        if not params and not params.params['name'] is None:
            sql_where.append(Role.name.contains(params.params['name']))
        query = session.query(Role).where(*sql_where)
        results = query.offset(offset).limit(params.size).all()
        data = []
        for result in results:
            data.append(RoleDto.from_orm(result))

        total = query.count()
        return PageResult(page=params.page, size=params.size, total=total, list=data)


# =====================   user   =======================


def insert_user(item: UserDto):
    user = user_dto2model(item)
    with Session(engine) as session, session.begin():
        session.add(user)


def update_user(item: UserDto):
    with Session(engine) as session, session.begin():
        session.execute(delete(assocation_table).where(assocation_table.c.user_id == item.id))
        for roleId in item.roleIds:
            session.execute(insert(assocation_table).values(user_id=item.id, role_id=roleId))
        return session.query(User).filter(User.id == item.id).update(
            {"name": item.name, "password": item.password}
        )


def delete_user(id: int):
    with Session(engine) as session, session.begin():
        session.execute(delete(assocation_table).where(assocation_table.c.user_id == id))
        return session.query(User).filter(User.id == id).delete()


def get_user(id: int):
    with Session(engine) as session:
        user = session.query(User).filter(User.id == id).first()
        return UserDto.from_orm(user)


def user_page(params: PageParams):
    with Session(engine) as session:
        offset = (params.page - 1) * params.size
        sql_where = []
        if not params and not params.params['name'] is None:
            sql_where.append(User.name.contains(params.params['name']))
        query = session.query(User).where(*sql_where)
        results = query.offset(offset).limit(params.size).all()
        data = []
        for result in results:
            data.append(UserDto.from_orm(result))

        total = query.count()
        return PageResult(page=params.page, size=params.size, total=total, list=data)
